﻿-- Function: pl_function.resumen_saldo_personal(character varying, refcursor)

-- DROP FUNCTION pl_function.resumen_saldo_personal(character varying, refcursor);

CREATE OR REPLACE FUNCTION pl_function.resumen_saldo_personal(p_mperson character varying, p_ref refcursor)
  RETURNS refcursor AS
$BODY$
begin
	open p_ref for select ax.cidpers,
		ax.ntotals,
		ax.npendie,
		ax.ncancel,
		ax.nporcen,
		case 
			when ax.nporcen::numeric(18, 2) <= 33.00 then '00ff00.png'
			when ax.nporcen::numeric(18, 2) <= 66.00 then 'ffff00.png'
			else 'ff0000.png'
		end vindica
	from (
		select a.cidpers
			, sum(a.imp_insol + a.costo_emis) ntotals
			, sum(case when b.estado='0' then a.imp_insol + a.costo_emis else 0 end) npendie
			, sum(case when b.estado='1' then a.imp_insol + costo_emis else 0 end) ncancel
			, case when sum(case when b.estado='0' then a.imp_insol + a.costo_emis else 0 end) = 0 then '0.00' else to_char(sum(a.imp_insol + a.costo_emis) / (sum(case when b.estado='0' then a.imp_insol + a.costo_emis else 0 end) * 100), 'FM999.00') end nporcen
		from public.mestados b
			inner join tesoreria.mestcta a on b.estado=a.nestado
			left join public.mconten c on a.ctiping=c.idsigma
		where a.cidpers=p_mperson
		group by a.cidpers
	) ax;
	return p_ref;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.resumen_saldo_personal(character varying, refcursor)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.resumen_saldo_personal(character varying, refcursor) TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.resumen_saldo_personal(character varying, refcursor) TO public;
GRANT EXECUTE ON FUNCTION pl_function.resumen_saldo_personal(character varying, refcursor) TO desarrollo;
